import pandas as pd
import sqlite3
from datetime import datetime

class SpendingAnalyzer:
    def __init__(self, db_path: str):
        self.conn = sqlite3.connect(db_path)
    
    def get_monthly_summary(self, year: int, month: int) -> dict:
        """获取月度消费摘要"""
        query = f"""
        SELECT category, SUM(amount) 
        FROM transactions 
        WHERE strftime('%Y', date) = '{year}' 
        AND strftime('%m', date) = '{month:02d}'
        GROUP BY category
        """
        df = pd.read_sql(query, self.conn)
        return df.to_dict('records')
    
    def detect_anomalies(self, threshold: float = 3.0) -> list:
        """检测异常交易"""
        df = pd.read_sql("SELECT * FROM transactions", self.conn)
        df['zscore'] = (df['amount'] - df['amount'].mean()) / df['amount'].std()
        return df[df['zscore'] > threshold].to_dict('records')

    def set_budget(self, category: str, amount: float) -> None:
        """为特定类别设置预算"""
        cursor = self.conn.cursor()
        cursor.execute("INSERT OR REPLACE INTO budgets (category, amount) VALUES (?, ?)",
                      (category, amount))
        self.conn.commit()

    def check_budget_overruns(self) -> list:
        """检查预算超支情况"""
        # 获取当前月份的消费数据
        current_month = datetime.now().strftime('%m')
        current_year = datetime.now().strftime('%Y')
        spending_query = f"""
        SELECT category, SUM(amount) as total_spent
        FROM transactions
        WHERE strftime('%Y', date) = '{current_year}'
        AND strftime('%m', date) = '{current_month}'
        GROUP BY category
        """

        spending_df = pd.read_sql(spending_query, self.conn)

        # 获取预算数据
        budget_df = pd.read_sql("SELECT category, amount as budget FROM budgets", self.conn)

        # 合并数据并检查超支
        combined_df = spending_df.merge(budget_df, on='category', how='left')
        combined_df['overrun'] = combined_df['total_spent'] > combined_df['budget']
        combined_df['overrun_amount'] = combined_df['total_spent'] - combined_df['budget']

        return combined_df[combined_df['overrun']].to_dict('records')